﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace sgsBase
{
	class datavals
	{
		public string SqlDataType;
		public string CType;
		public string GetAt;
		public bool TS = false;

		public datavals(string sqlDataType, string cType, string getAt)
		{
			SqlDataType = sqlDataType;
			CType = cType;
			GetAt = getAt;
		}
		public datavals(string sqlDataType, string cType, string getAt, bool ts)
		{
			SqlDataType = sqlDataType;
			CType = cType;
			GetAt = getAt;
			TS = ts;
		}
		public datavals()
		{
		}
	} 




    public class appClassGen
    {

		private Dictionary<string, datavals> _data_types = new Dictionary<string, datavals>();

		public string Namespace = "";
		public string Database = "";
        public appClassGen()
        {
			_data_types.Add("money",   new datavals("Money","decimal","DecimalAt"));
			_data_types.Add("int",     new datavals("Int","int","IntAt"));
			_data_types.Add("decimal", new datavals("Decimal","decimal","DecimalAt"));
			_data_types.Add("text",    new datavals("Text","String","StringAt"));
			_data_types.Add("smallint", new datavals("Int","int","IntAt"));
			_data_types.Add("datetime", new datavals("DateTime","DateTime","DateTimeTSAt", true));
			_data_types.Add("varchar", new datavals("VarChar","String","StringAt"));
			_data_types.Add("tinyint", new datavals("Int","int","IntAt"));
			_data_types.Add("float",   new datavals("Float","double","DoubleAt"));
			_data_types.Add("real",    new datavals("Real","double","DoubleAt"));
			_data_types.Add("char",    new datavals("Char","String","StringAt"));
			_data_types.Add("bigint",  new datavals("BigInt","long","LongAt"));
			_data_types.Add("bit",     new datavals("Bit","int","IntAt"));
			_data_types.Add("nvarchar", new datavals("NVarChar","String","StringAt"));
			_data_types.Add("nchar", new datavals("NChar", "String", "StringAt"));
			_data_types.Add("date", new datavals("Date", "DateTime", "DateAt"));
			_data_types.Add("timestamp", new datavals("Timestamp", "DateTime", "DateTimeTSAt", true));
        }

        public string genClass(string table, string classname)
        {
			string[] ra = table.Split('.');
			bool isTable = (ra[0] == "U") ? true : false;
			string schema = ra[1];
			table = ra[2];
            StringWriter sw = new StringWriter();
            sw.WriteLine("using System;");
            sw.WriteLine("using System.Collections.Generic;");
            sw.WriteLine("using System.Text;");
            sw.WriteLine("using System.Data;");
            sw.WriteLine("using System.Data.Sql;");
			sw.WriteLine("using sgsBase;");
			sw.WriteLine(" ");
            sw.WriteLine("namespace " );
            sw.WriteLine("{");
            sw.Write("[Serializable] public class " + classname + " : ");
			if (isTable)
				sw.WriteLine(" DbObject");
			else
				sw.WriteLine(" DbViewObject");

            sw.WriteLine("{");
            sw.WriteLine("  public " + classname + "() : base()");
            sw.WriteLine("  {");
            sw.WriteLine("      Table = \"" + schema + "." + table + "\";");

			if (Database != "")
				sw.WriteLine("      Database = \"" + Database + "\";");

            // code for each table column

            //sw.Write(dothis(table));
			sw.Write(MapToTable(table, schema));

            return sw.ToString() ;
        }

		private string MapToTable(string table, string schema)
		{
			List<string> PKs = new List<string>(); // list of primary keys
			bool identity = false;
			string qtable = DbInterface.quote(table);
			schema = DbInterface.quote(schema);

			DbInterface dbi = new DbInterface(Database);
			string sql = "select COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS";
			sql += " where table_name = " + qtable + " and table_schema=" + schema + " order by ORDINAL_POSITION";
			DataTable columns = dbi.Query(sql);

			sql = "select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME=" + qtable + " and table_schema=" + schema;

			DataTable keys = dbi.Query(sql);

			//if only 1 primary key, it may be an identity. Check and see
			if (keys.Rows.Count == 1)
			{
				sql = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS ";
				sql += "where TABLE_SCHEMA = " + schema + " and TABLE_NAME=" + qtable;
				sql += "and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 ";

				DataTable Idents = dbi.Query(sql);

				if (Idents.Rows.Count > 0)
					identity = true;
			}

			foreach (DataRow dr in keys.Rows)
			{
				PKs.Add(dr["COLUMN_NAME"].ToString());
			}

			string columnname = "";
			string datatype = "";
			StringWriter sw = new StringWriter();
			StringWriter sw2 = new StringWriter();

			int i = 0;
			foreach (DataRow dr in columns.Rows)
			{
				columnname = dr["COLUMN_NAME"].ToString();
				datatype = dr["DATA_TYPE"].ToString();

				sw.WriteLine(AddColString(columnname, datatype, PKs, identity));
				sw2.WriteLine(getset(i, columnname, _data_types[datatype].CType, _data_types[datatype].GetAt, _data_types[datatype].TS));
				i++;
			}

			sw.WriteLine("  }");
			sw.WriteLine("  ");
			sw.WriteLine("  #region Properties");
			sw.WriteLine("  ");
			sw.Write(sw2.ToString());
			sw.WriteLine("  ");
			sw.WriteLine("  #endregion");
			sw.WriteLine("}");
			sw.WriteLine("}");

			return sw.ToString();
		}

		private string AddColString(string columnname, string datatype, List<string> PKs, bool identity)
		{
			string ret = "      Columns.AddColumn(\"" + columnname + "\", SqlDbType.";
			string ispk = PKs.Find(delegate(string spk)
			{
				return spk == columnname;
			}
			);

			ret += _data_types[datatype].SqlDataType;

			if (ispk != null)
			{
				ret += ", true, " + identity.ToString().ToLower(); 
			}
			ret += ");";
			return ret;
		}

        private string getset(int i, string fieldname, string rettype, string ftype, bool ts)
        {
            StringWriter sw = new StringWriter();

            sw.WriteLine("      public " + rettype + " " + debuff(fieldname));
            sw.WriteLine("      {");
            sw.WriteLine("          get { return Columns." + ftype + "(" + i + ");}");
			if (ts)
				sw.WriteLine("          set { Columns.SetValAt(" + i + ", value, true);}");
			else
				sw.WriteLine("          set { Columns.SetValAt(" + i + ", value);}");
            sw.WriteLine("      }");
            
            return sw.ToString();
        }

        private string debuff(string fieldname)
        {
            string[] sra = fieldname.Split('_');
            string ret = "";

            foreach (string s in sra)
            {
                ret += char.ToUpper(s[0]) + s.Substring(1);
            }
            return ret;
        }
    }
}
